## 14.2. Simple Database Access

The `Connect` example did not do much. It simply showed you how to connect to a database. A database connection is useless unless you actually talk to the database. The simplest forms of database access are `SELECT`, `INSERT`, `UPDATE`, and `DELETE` statements. Under the JDBC API, you use your database `Connection` instance to create `Statement` instances. A `Statement` naturally represents any kind of SQL statement. Example 14-1 shows how to insert a row into a database using a `Statement`.

#### Example 14-1. Inserting a Row into mSQL Using a JDBC Statement Object

>     import java.sql.*;
>     import java.util.*;

>     public class Insert {
>         // We are inserting into a table that has two columns: test_id (int)
>         // and test_val (char(55))
>         // args[0] is the test_id and args[1] the test_val
>         public static void main(String argv[]) {
>             Connection con = null;
>             ResourceBundle bundle = ResourceBundle.getBundle("SelectResource");

>             try {
>                 String url = bundle.getString("URL");
>                 Statement stmt;

>                 Class.forName(bundle.getString("Driver"));
>                 // here is where the connection is made
>                 con = DriverManager.getConnection(url, "borg", "");
>                 stmt = con.createStatement();
>                 stmt.executeUpdate("INSERT INTO test (test_id, test_val) " +
>                                    "VALUES(" + args[0] + ", '" + args[1] + "')");
>             }
>             catch( SQLException e ) {
>                 e.printStackTrace();
>             }
>             finally {
>                 if( con != null ) {
>                     try { con.close(); }
>                     catch( Exception e ) { }
>                 }
>             }
>         }
>     }

If this were a real application, we would of course verified that the user entered an `INT` for the `test_id`, that it was not a duplicate key, and that the `test_val` entry did not exceed 55 characters. This example nevertheless shows how simple performing an insert is. The `createStatement()` method does just what it says: it creates an empty SQL statement associated with the `Connection` in question. The `executeUpdate()` method then passes the specified SQL on to the database for execution. As its name implies, `executeUpdate()` expects SQL that will be modifying the database in some way. You can use it to insert new rows as shown earlier, or instead to delete rows, update rows, create new tables, or do any other sort of database modification.

Queries are a bit more complicated than updates because queries return information from the database in the form of a `ResultSet`. A `ResultSet` is an interface that represents zero or more rows matching a database query. A JDBC `Statement` has an `executeQuery()` method that works like the `executeUpdate()` method -- except it returns a `ResultSet` from the database. Exactly one `ResultSet` is returned by `executeQuery()`, however, you should be aware that JDBC supports the retrieval of multiple result sets for databases that support multiple result sets. Neither MySQL or mSQL support multiple result sets. It is nevertheless important for you to be aware of this issue in case you are ever looking at someone else's code written against another database engine. Example 14-2 shows a simple query. Figure 14-1 shows the data model behind the test table.

#### Example 14-2. A Simple Query

>     import java.sql.*;
>     import java.util.*;

>     public class Select {
>         public static void main(String argv[]) {
>             Connection con = null;
>             ResourceBundle bundle =
>                   ResourceBundle.getBundle("SelectResource");

>             try {
>                 String url = bundle.getString("URL");
>                 Statement stmt;
>                 ResultSet rs;

>                  Class.forName(bundle.getString("Driver"));
>                 // here is where the connection is made
>                 con = DriverManager.getConnection(url, "borg", "");
>                 stmt = con.createStatement();
>                 rs = stmt.executeQuery("SELECT * from test ORDER BY test_id");
>                 System.out.println("Got results:");
>                 while(rs.next()) {
>                     int a= rs.getInt("test_id");
>                     String str = rs.getString("test_val");

>                     System.out.print(" key= " + a);
>                     System.out.print(" str= " + str);
>                     System.out.print("\n");
>                 }
>                 stmt.close();
>             }
>             catch( SQLException e ) {
>                 e.printStackTrace();
>             }
>             finally {
>                 if( con != null ) {
>                     try { con.close(); }
>                     catch( Exception e ) { }
>                 }
>             }
>         }
>     }

#### Figure 14-1. The test table from the sample database

The `Select` application executes the query and then loops through each row in the `ResultSet` using the `next()` method. Until the first call to `next()`, the `ResultSet` does not point to any row. Each call to `next()` points the `ResultSet` to the subsequent row. JDBC 2.0 introduces the concept of a scrollable result set. If your `ResultSet` instance is set to be scrollable, you can also make calls to `previous()` to navigate backwards through the results. You are done processing rows when `next()` returns `false`.

Dealing with a row means getting the values for each of its columns. Whatever the value in the database, you can use the getter methods in the `ResultSet` to retrieve the column value as whatever Java datatype you like. In the `Select` application, the call to `getInt()` returned the `test_id` column as an `int` and the call to `getString()` returned the `test_val` column as a `String`. These getter methods accept either the column number -- starting with column 1 -- or the column name. You should, however, avoid retrieving values using a column name at all costs since retrieving results by column name is many, many times slower than retrieving them by column number.

### 14.2.1. Error Handling and Clean Up

All JDBC method calls can throw `SQLException` or one of its subclasses if something happens during a database call. Your code should be set up to catch this exception, deal with it, and clean up any database resources that have been allocated. Each of the JDBC classes mentioned so far has a `close()` method associated with it. Practically speaking, however, you only really need to make sure you close things whose calling process might remain open for a while. In the examples we have seen so far, you only really need to close your database connections. Closing the database connection closes any statements and result sets associated with it automatically. If you intend to leave a connection open for any period of time, however, it is a good idea to go ahead and close the statements you create using that connection when you finish with them. In the JDBC examples you have seen, this clean up happens in a finally clause. You do this since you want to make sure to close the database connection no matter what happens.

Copyright © 2001 O'Reilly & Associates. All rights reserved.